﻿using System;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Configuration;
using System.Xml;
using System.Xml.XPath;

/// <summary>
/// Класс для связи с БД
/// </summary>

public class DBControl
{
    private String connectionString;

    public DBControl()
    {
        this.connectionString = WebConfigurationManager.ConnectionStrings["TestBase"].ConnectionString;
    }

    public DBControl(String connectionString)
    {
        this.connectionString = connectionString;
    }

    public CategoryDetails GetCategory(Int64 categoryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetCategory", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            reader.Read();
            CategoryDetails cat = new CategoryDetails((Int64)reader["id"], (Int64)reader["parent_id"],
                HttpUtility.HtmlEncode((String)reader["name"]), ((Byte)reader["show_in_short_list"] == 1), (Int32)reader["order_n"]);
            reader.Close();

            return cat;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public List<CategoryDetails> GetCategoryList()
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetCategoryList", con);
        cmd.CommandType = CommandType.StoredProcedure;
        List<CategoryDetails> categories = new List<CategoryDetails>();

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            CategoryDetails cat = new CategoryDetails(0, -1, "Главная", true, 0);
            categories.Add(cat);

            while (reader.Read())
            {
                cat = new CategoryDetails((Int64)reader["id"], (Int64)reader["parent_id"],
                    (String)reader["name"], ((Byte)reader["show_in_short_list"]).Equals(1), (Int32)reader["order_n"]);
                categories.Add(cat);
            }
            reader.Close();

            return categories;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public List<CategoryDetails> GetChildCategoryList(Int64 parentId, Boolean shortList)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetChildCategoryList", con);
        cmd.CommandType = CommandType.StoredProcedure;
        List<CategoryDetails> categories = new List<CategoryDetails>();
        cmd.Parameters.Add(new SqlParameter("@parent_id", SqlDbType.BigInt));
        cmd.Parameters["@parent_id"].Value = parentId;
        cmd.Parameters.Add(new SqlParameter("@short_list", SqlDbType.TinyInt));
        if (shortList)
        {
            cmd.Parameters["@short_list"].Value = 1;
        }
        else
        {
            cmd.Parameters["@short_list"].Value = 0;
        }

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                CategoryDetails cat = new CategoryDetails((Int64)reader["id"], (Int64)reader["parent_id"],
                    HttpUtility.HtmlEncode((String)reader["name"]), ((Byte)reader["show_in_short_list"]).Equals(1), (Int32)reader["order_n"]);
                categories.Add(cat);
            }
            reader.Close();

            return categories;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public Int64 InsertCategory(Int64 parentId, String categoryName, Boolean showInShortList)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("InsertCategory", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@parent_id", SqlDbType.BigInt));
        cmd.Parameters["@parent_id"].Value = parentId;
        cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar));
        cmd.Parameters["@name"].Value = categoryName;
        cmd.Parameters.Add(new SqlParameter("@show_in_short_list", SqlDbType.TinyInt));
        if (showInShortList)
        {
            cmd.Parameters["@show_in_short_list"].Value = 1;
        }
        else
        {
            cmd.Parameters["@show_in_short_list"].Value = 0;
        }
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

            return (Int64)cmd.Parameters["@category_id"].Value;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void UpdateCategory(Int64 categoryId, Int64 parentId, String categoryName, Boolean showInShortList)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UpdateCategory", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;
        cmd.Parameters.Add(new SqlParameter("@parent_id", SqlDbType.BigInt));
        cmd.Parameters["@parent_id"].Value = parentId;
        cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar));
        cmd.Parameters["@name"].Value = categoryName;
        cmd.Parameters.Add(new SqlParameter("@show_in_short_list", SqlDbType.TinyInt));
        if (showInShortList)
        {
            cmd.Parameters["@show_in_short_list"].Value = 1;
        }
        else
        {
            cmd.Parameters["@show_in_short_list"].Value = 0;
        }

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void DeleteCategory(Int64 categoryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("DeleteCategory", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public List<SiteDetails> GetChildSiteList(Int64 parentId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetChildSiteList", con);
        cmd.CommandType = CommandType.StoredProcedure;
        List<SiteDetails> sites = new List<SiteDetails>();
        cmd.Parameters.Add(new SqlParameter("@parent_id", SqlDbType.BigInt));
        cmd.Parameters["@parent_id"].Value = parentId;

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                SiteDetails site = new SiteDetails((Int64)reader["id"], (Int64)reader["parent_id"],
                    (String)reader["name"], (String)reader["desc_short"], (String)reader["desc_full"],
                     (String)reader["region"], (String)reader["email"], (String)reader["url"]);
                sites.Add(site);
            }
            reader.Close();

            return sites;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public List<UserDetails> GetUserList()
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetUserList", con);
        cmd.CommandType = CommandType.StoredProcedure;
        List<UserDetails> users = new List<UserDetails>();
        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                UserDetails user = new UserDetails((Int64)reader["id"], (String)reader["login"]);
                users.Add(user);
            }
            reader.Close();

            return users;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public Boolean AuthUser(String userLogin, String userPsswd)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("AuthUser", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.NVarChar));
        cmd.Parameters["@login"].Value = userLogin;
        cmd.Parameters.Add(new SqlParameter("@psswd", SqlDbType.NVarChar));
        cmd.Parameters["@psswd"].Value = FormsAuthentication.HashPasswordForStoringInConfigFile(userPsswd, "SHA1");
        cmd.Parameters.Add(new SqlParameter("@val_user", SqlDbType.TinyInt));
        cmd.Parameters["@val_user"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

            return ((Byte)cmd.Parameters["@val_user"].Value).Equals(1);
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public Int64 InsertUser(String userLogin, String userPsswd)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("InsertUser", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.NVarChar));
        cmd.Parameters["@login"].Value = userLogin;
        cmd.Parameters.Add(new SqlParameter("@psswd", SqlDbType.NVarChar));
        cmd.Parameters["@psswd"].Value = FormsAuthentication.HashPasswordForStoringInConfigFile(userPsswd, "SHA1");
        cmd.Parameters.Add(new SqlParameter("@user_id", SqlDbType.BigInt));
        cmd.Parameters["@user_id"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

            return (Int64)cmd.Parameters["@user_id"].Value;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void UpdateUser(Int64 userId, String userLogin)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UpdateUser", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@user_id", SqlDbType.BigInt));
        cmd.Parameters["@user_id"].Value = userId;
        cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.NVarChar));
        cmd.Parameters["@login"].Value = userLogin;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void DeleteUser(Int64 userId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("DeleteUser", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@user_id", SqlDbType.BigInt));
        cmd.Parameters["@user_id"].Value = userId;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void SetUserPsswd(Int64 userId, String userPsswd)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SetUserPsswd", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@user_id", SqlDbType.BigInt));
        cmd.Parameters["@user_id"].Value = userId;
        cmd.Parameters.Add(new SqlParameter("@psswd", SqlDbType.NVarChar));
        cmd.Parameters["@psswd"].Value = FormsAuthentication.HashPasswordForStoringInConfigFile(userPsswd, "SHA1");

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public Boolean UserExists(String userLogin)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UserExists", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.NVarChar));
        cmd.Parameters["@login"].Value = userLogin;
        cmd.Parameters.Add(new SqlParameter("@user_exists", SqlDbType.TinyInt));
        cmd.Parameters["@user_exists"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

            return ((Byte)cmd.Parameters["@user_exists"].Value).Equals(1);
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public XmlDocument getCategoriesXml(long pos)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetCategoryList", con);
        cmd.CommandType = CommandType.StoredProcedure;

        XmlDocument categoriesXml = new XmlDocument();
        XPathNavigator categoriesXmlNav = categoriesXml.CreateNavigator();
        XPathNodeIterator categoriesIter;

        categoriesXmlNav.AppendChild("<category id=\"\" name=\"\"/>");
        categoriesXmlNav.MoveToFirstChild();
        categoriesXmlNav.MoveToAttribute("id", "");
        categoriesXmlNav.SetTypedValue(pos);
        categoriesXmlNav.MoveToParent();
        categoriesXmlNav.MoveToAttribute("name", "");
        categoriesXmlNav.SetTypedValue("Главная");
        categoriesXmlNav.MoveToParent();

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                categoriesIter = categoriesXmlNav.Select("//category[@id=\"" + ((Int64)reader["parent_id"]).ToString() + "\"]");

                if (categoriesIter.MoveNext())
                {
                    categoriesIter.Current.AppendChild("<category id=\"\" name=\"\"/>");
                    categoriesIter.Current.MoveToFirstChild();
                    while (categoriesIter.Current.MoveToNext()) ;

                    categoriesIter.Current.MoveToAttribute("id", "");
                    categoriesIter.Current.SetTypedValue((Int64)reader["id"]);
                    categoriesIter.Current.MoveToParent();
                    categoriesIter.Current.MoveToAttribute("name", "");
                    categoriesIter.Current.SetTypedValue(HttpUtility.HtmlEncode((String)reader["name"]));
                    categoriesIter.Current.MoveToParent();
                }
            }
            reader.Close();

            return categoriesXml;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public Int32 GetChildCount(Int64 categoryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetChildCount", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;
        cmd.Parameters.Add(new SqlParameter("@child_count", SqlDbType.Int));
        cmd.Parameters["@child_count"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();

            return (Int32)cmd.Parameters["@child_count"].Value;
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void MoveCategoryUp(Int64 categoryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("MoveCategoryUp", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }

    public void MoveCategoryDown(Int64 categoryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("MoveCategoryDown", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@category_id", SqlDbType.BigInt));
        cmd.Parameters["@category_id"].Value = categoryId;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            //WriteErroorLog(e.Message);
            throw new ApplicationException("Ошибка БД");
        }
        finally
        {
            con.Close();
        }
    }
}
